package com.bw.mapper;

import com.bw.bean.Mover;
import com.bw.bean.Mover1;
import com.bw.bean.Mover2;
import com.bw.bean.Mover3;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface KcMapper {

    @Select("SELECT s.name , sum(oi.original_amount) num1 , count(oi.id) count1 , count(p.id) count2 , sum(oi.actual_amount) num2 , sum(p.amount) num3 from order_info oi\n" +
            "left join shop s on oi.shop_id = s.id\n" +
            "left join payment p on oi.id = p.order_info_id\n" +
            "where status = 5\n" +
            "GROUP BY s.name;")
    List<Mover> getList1();

    @Select("SELECT curdate() time, 1 day ,\n" +
            "    sum(case when s.type =1 then p.reduce_amount end) amount1 ,\n" +
            "    sum(case when s.type =2 then p.reduce_amount end) amount2,\n" +
            "    sum(p.reduce_amount) amount3\n" +
            "from shop s left join order_info oi on  s.id = oi.shop_id\n" +
            "left join promotion p on oi.promotion_id = p.id\n" +
            "where  timestampdiff(year,p.create_time,now())<=1;")
    List<Mover1> getList2();


    @Select("select p.name , avg(oi.rating) value from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_info_id\n" +
            "left join product_sku ps on od.product_sku_id = ps.id\n" +
            "left join product_spu p on ps.product_spu_id = p.id\n" +
            "where p.name is not null\n" +
            "group by p.name;")
    List<Mover2> getList3();

    @Select("SELECT hour(create_time) name , sum(original_amount) value  from order_info\n" +
            "GROUP BY hour(create_time);")
    List<Mover3> getList4();
}
